package database;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


import bean.CategorySubCategoryBean;
import bean.UserBean;
import com.sun.xml.internal.fastinfoset.tools.StAX2SAXReader;
import java.sql.*;
import java.io.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.*;
import javax.servlet.http.*;

/**
 *
 * @author user
 */
public class database extends HttpServlet {
   
    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected final String driver = "com.mysql.jdbc.Driver";
    protected final String dbName = "maninrelaychat";
    protected final String dbUserName = "root";
    protected final String dbPassword ="";

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException, Exception {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            try {
                Connection connection = getConnection();
                String paramQuery = request.getParameter("query");
                if (paramQuery.equals("register")) {
                    String name = request.getParameter("name");
                    String username = request.getParameter("username");
                    String password = request.getParameter("password");
                    String email = request.getParameter("email");
                    int userNameType = 0;
                    int max_room = 20;
                    int ban = 0;

                    String query = "INSERT INTO user (name, username, username_type, password, email,max_room, ban) values ('" + name + "', '" + username + "','" + userNameType + "', '" + password + "', '" + email + "', '" + max_room + "', '" + ban + "')";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("index.jsp");
                } else if (paramQuery.equals("edit")) {
                    String userName = request.getParameter("edit_username");
                    String name = request.getParameter("edit_name");
                    String email = request.getParameter("edit_email");
                    String password = request.getParameter("edit_password");
                    String maxroom = request.getParameter("edit_maxroom");
                    String status = request.getParameter("status");
                    int maxroomint = Integer.parseInt(maxroom);
                    int ban = Integer.parseInt(status);
                    
                    String query = "UPDATE user SET name='"+name+"',email='"+email+"',password='"+password+"',max_room='"+maxroomint+"',ban='"+ban+"' WHERE username='"+userName+"'";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("delete")) {
                    String userName = request.getParameter("delete_username");
                    
                    String query = "DELETE FROM user  WHERE username='"+userName+"'";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("add")) {
                    String name = request.getParameter("add_name");
                    String username = request.getParameter("add_username");
                    String password = request.getParameter("add_password");
                    String email = request.getParameter("add_email");
                    int userNameType = 0;
                    String maxRoom = request.getParameter("add_maxroom");
                    int max_room = Integer.parseInt(maxRoom);
                    int ban = 0;
                    
                    String query = "INSERT INTO user (name, username, username_type, password, email,max_room, ban) values ('" + name + "', '" + username + "','" + userNameType + "', '" + password + "', '" + email + "', '" + max_room + "', '" + ban + "')";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("addcat")) {
                    String name = request.getParameter("add_category_name");
                    String moderator = request.getParameter("add_category_moderator");
                    String par = request.getParameter("category_parent");
                    String parent = "";
                    if (par.equals("0")) {
                        parent = "null";
                    } else {
                        ArrayList<CategorySubCategoryBean> categories = null;
                        DataManager dataManager = new DataManager();
                        categories = dataManager.getAllCategory();

                        int parIdx = Integer.parseInt(par);
                        parent = categories.get(parIdx-1).getName();
                    }
                    
                    String query = "INSERT INTO category (name, parent, moderator) values ('" + name + "', '" + parent + "','" + moderator + "')";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("editcat")) {
                    String name = request.getParameter("edit_category_name");
                    String moderator = request.getParameter("edit_category_moderator");
                    String par = request.getParameter("edit_category_parent");
                    String parent = "";
                    if (par.equals("0")) {
                        parent = "null";
                    } else {
                        ArrayList<CategorySubCategoryBean> categories = null;
                        DataManager dataManager = new DataManager();
                        categories = dataManager.getAllCategory();

                        int parIdx = Integer.parseInt(par);
                        parent = categories.get(parIdx-1).getName();
                    }

                    String query = "UPDATE category SET parent='"+parent+"', moderator='" + moderator + "' WHERE name='"+name+"' ";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("deletecat")) {
                    String name = request.getParameter("delete_category_name");
                    String query = "DELETE FROM category  WHERE name='"+name+"'";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("perintah")) {
                    String inputbox = request.getParameter("inputbox");
                    String[] token = inputbox.split(" ");
                    if (token[0].equals("/addfriend"))
                    {
                        String query =
                    }
                    
                    String username = request.getParameter("add_username");
                    String password = request.getParameter("add_password");
                    String email = request.getParameter("add_email");
                    int userNameType = 0;
                    String maxRoom = request.getParameter("add_maxroom");
                    int max_room = Integer.parseInt(maxRoom);
                    int ban = 0;

                    String query = "INSERT INTO user (name, username, username_type, password, email,max_room, ban) values ('" + name + "', '" + username + "','" + userNameType + "', '" + password + "', '" + email + "', '" + max_room + "', '" + ban + "')";
                    Statement statement = connection.createStatement();
                    int res = statement.executeUpdate(query);
                    connection.close();
                    response.sendRedirect("admin.jsp");
                } else if (paramQuery.equals("login")) {
                    String userName = request.getParameter("login_username");
                    String password = request.getParameter("login_password");

                    String query = null;
                    query = "SELECT * FROM user WHERE username = '" + userName + "' AND password ='" + password + "'";
                    Statement statement = connection.createStatement();
                    ResultSet resultSet = statement.executeQuery(query);
                    if (resultSet.next()) {
                        if (!(userName.equals("null")) && userName.equals(resultSet.getString("username")) && !(password.equals("null")) && password.equals(resultSet.getString("password"))) {
                            HttpSession session = request.getSession(true);
                            String username = resultSet.getString("username");
                            String name = resultSet.getString("name");
                            String pass = resultSet.getString("password");
                            String email = resultSet.getString("email");
                            String usertype = resultSet.getString("username_type");
                            String status =  resultSet.getString("ban");
                                    
                            session.setAttribute("username", username);
                            session.setAttribute("name", name);
                            session.setAttribute("password", pass);
                            session.setAttribute("email", email);
                            session.setAttribute("usertype", usertype);
                            session.setAttribute("status", status);
                            
                            if (usertype.equals("0")) {
                                if (status.equals("0")) { //gak ke ban
                                    response.sendRedirect("home.jsp");
                                }
                                else {
                                    response.sendRedirect("index.jsp");
                                }
                            } else {
                                response.sendRedirect("admin.jsp");
                            }
                        } else {
                            response.sendRedirect("index.jsp");
                        }
                    } else {
                        response.sendRedirect("index.jsp");
                    }
                } else if (paramQuery.equals("logout")) {
                    HttpSession session = request.getSession(false);
                    if (session != null) {
                        session.invalidate();
                        response.sendRedirect("index.jsp");
                    }
                } else {
                    //TODO output your page here
                    out.println("<html>");
                    out.println("<head>");
                    out.println("<title>No Page Request</title>");
                    out.println("</head>");
                    out.println("<body>");
                    out.println("<h1>No Page!</h1>");
                    out.println("</body>");
                    out.println("</html>");
                }


            } catch (SQLException e) {
                e.printStackTrace();
            }

        } finally {
            out.close();
        }
    }

    protected Connection getConnection()
           throws  Exception{
            Class.forName(driver);
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + dbName, dbUserName, dbPassword);
        return connection;

    }
   


    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(database.class.getName()).log(Level.SEVERE, null, ex);
        }
       
    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}
